/*******************************************************************************
 * Copyright (c) 2005, 2014 springside.github.io
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package com.yihu.wlyy.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.yihu.wlyy.entity.doctor.Doctor;
import com.yihu.wlyy.entity.doctor.DoctorPatientGroup;
import com.yihu.wlyy.entity.doctor.DoctorPatientGroupInfo;
import com.yihu.wlyy.entity.patient.Patient;

public interface DoctorDao extends PagingAndSortingRepository<Doctor, Long>, JpaSpecificationExecutor<Doctor> {

	Doctor findByName(String name);

	// 按医生标识查询医生信息
	@Query("select p from Doctor p where p.code = ?1")
	Doctor findByCode(String code);

	@Query("select d from Doctor d where d.del = 1")
	List<Doctor> findAllDoctors();

	@Query("select d from Doctor d where d.del = 1 and d.hospital = ?1")
	Iterable<Doctor> findHospitalDoctors(String hospital);

	// 手机号查询医生信息
	@Query("select p from Doctor p where p.mobile = ?1")
	Doctor findByMobile(String mobile);

	// 根据医生分组查询患者信息
	@Query("select a.group, b.code, b.name, b.photo, b.birthday, b.sex, b.diseaseCondition, b.disease, b.recordAmount, a.partAmount, a.qyrq, a.signType,b.idcard from DoctorPatientGroupInfo a, Patient b where a.doctor = ?1 and a.patient = b.code and a.status=1")
	List<Object> findPatientByDoctorSign(String doctor);
	
	// 根据医生分组查询患者信息
	@Query("select distinct a.patient , a.id, b.name, b.photo,b.provinceName,b.cityName,b.townName,b.address, a.begin,b.code,b.mobile,a.signType,b.idcard from SignFamily a, Patient b where (a.doctor = ?1 or a.doctorHealth = ?1) and a.patient = b.code and a.status=1 ORDER BY a.id desc")
	Page<Object> findPatientByDoctorSign(String doctor, Pageable pageRequest);
	
	// 根据医生分组查询患者信息
	@Query("select distinct a.patient ,a.id, b.name, b.photo,b.provinceName,b.cityName,b.townName,b.address, a.begin,b.code,b.mobile,a.signType,b.idcard from SignFamily a, Patient b where (a.doctor = ?1 or a.doctorHealth = ?1) and a.patient = b.code and a.id< ?2 and a.status=1  ORDER BY a.id desc")
	Page<Object> findPatientByDoctorSign(String doctor,long id, Pageable pageRequest);

	// 根据医生分组查询患者信息
	@Query("select distinct a.patient ,a.id, b.name, b.photo,b.provinceName,b.cityName,b.townName,b.address, a.begin,b.code,b.mobile, a.signType,b.idcard  from SignFamily a, Patient b where (a.doctor = ?1 or a.doctorHealth = ?1) and a.patient = b.code and a.status=1 and b.name like ?2 and a.status=1 ORDER BY a.id desc")
	Page<Object> findPatientByDoctorSign(String doctor,String patientName, Pageable pageRequest);
	
	// 根据医生分组查询患者信息
	@Query("select distinct a.patient ,a.id, b.name, b.photo,b.provinceName,b.cityName,b.townName,b.address, a.begin,b.code,b.mobile,a.signType,b.idcard  from SignFamily a, Patient b where (a.doctor = ?1 or a.doctorHealth = ?1) and a.patient = b.code and a.id<?2 and a.status=1 and b.name like ?3 and a.status=1  ORDER BY a.id desc")
	Page<Object> findPatientByDoctorSign(String doctor,long id,String patientName, Pageable pageRequest);
	
	// 查询医生下的患者总数
	@Query("select count(distinct a.patient) from DoctorPatientGroupInfo a where a.doctor = ?1 and a.status=1")
	int amountPatientByDoctorSign(String doctor);

	@Query("select p from DoctorPatientGroup p where p.doctor = ?1  and p.status = 1")
	Iterable<DoctorPatientGroup> findDoctorPatientGroupWithOutNull(String doctor);
	
	@Query("select p from DoctorPatientGroup p where (p.doctor = ?1 or p.doctor is null) and p.status = 1 order by p.id asc")
	Iterable<DoctorPatientGroup> findDoctorPatientGroup(String doctor);

	// 根據患者標識查詢患者信息
	@Query("select p from Patient p where p.code=?1")
	Patient findPatientByCode(String code);

	// 查询患者与医生的签约关系
	@Query("select p from DoctorPatientGroupInfo p where p.patient=?1 and p.doctor = ?2")
	DoctorPatientGroupInfo findGroup(String user, String doctor);
	
	@Query("from Doctor p where p.hospital=?1 and p.level = ?2 and p.iscertified=1 and status=1 ")
	List<Doctor> getDoctorCombobxByHospital(String hospital, int type);

	@Query("from Doctor p where p.level = 1 and p.iscertified=1 and p.status=1 ")
	Page<Doctor> findDoctor1ByParient(Pageable pageRequest);

	@Query("from Doctor p where p.level = 1 and p.iscertified=1 and p.status=1 and p.code != ?1")
	Page<Doctor> findDoctor1ByParient(String code ,Pageable pageRequest);

	@Query("select d from Doctor d ,DrHealthTeamMember dtm where d.code=dtm.memberCode and dtm.signType='1' and dtm.team=?1  and dtm.del='1'")
	List<Doctor> findSanshiDoctorByTeam(String code);


	@Query("select d from Doctor d ,DrHealthTeamMember dtm where d.code=dtm.memberCode and dtm.signType='2' and dtm.team=?1  and dtm.del='1'")
	List<Doctor> findJiatingDoctorByTeam(String code);

	// 手机号查询正常医生信息
	@Query("select p from Doctor p where p.mobile = ?1 and p.del=1")
	Doctor findNormalByMobile(String mobile);

	@Query("select p from Doctor p where p.level=2 and p.del=1 ")
	List<Doctor> findAllQKDoctot();

	@Query("select p from Doctor p where p.level=3 and p.del=1 ")
	List<Doctor> findAllZKDoctot();

	@Query("select p from Doctor p where p.level=1 and p.del=1 ")
	List<Doctor> findAllJKDoctot();


	@Query("select d from Doctor d where d.del = 1 and d.iscertified=1")
	List<Doctor> findAllCertifiedDoctors();

	@Query("select p from Doctor p where p.level in (2,3) and p.del=1 and p.iscertified=1")
	List<Doctor> findAllQKDoctotAndJKDoctor();

	@Query(" select p from Doctor p where p.hospital =?1 and p.level =?2 and p.del=1  ")
	List<Doctor> findDoctorByLevelAndHospital(String hospital, Integer level);

	@Modifying
	@Query("update Doctor set idcard = ?2 where code = ?1")
	int updateIdcard(String doctor,String idcard);
}
